package glgc.jjgys.system.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import glgc.jjgys.common.excel.ExcelUtil;
import glgc.jjgys.model.project.JjgFbgcSdgcCqtqd;
import glgc.jjgys.model.projectvo.ljgc.CommonInfoVo;
import glgc.jjgys.model.projectvo.sdgc.JjgFbgcSdgcCqtqdVo;
import glgc.jjgys.model.system.SysUser;
import glgc.jjgys.system.easyexcel.ExcelHandler;
import glgc.jjgys.system.exception.JjgysException;
import glgc.jjgys.system.mapper.JjgFbgcSdgcCqtqdMapper;
import glgc.jjgys.system.service.JjgFbgcLjgcHdgqdService;
import glgc.jjgys.system.service.JjgFbgcSdgcCqtqdService;
import glgc.jjgys.system.service.SysUserService;
import glgc.jjgys.system.utils.JjgFbgcCommonUtils;
import glgc.jjgys.system.utils.ReceiveUtils;
import glgc.jjgys.system.utils.RowCopy;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.StandardCopyOption;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author wq
 * @since 2023-03-26
 */
@Service
public class JjgFbgcSdgcCqtqdServiceImpl extends ServiceImpl<JjgFbgcSdgcCqtqdMapper, JjgFbgcSdgcCqtqd> implements JjgFbgcSdgcCqtqdService {

    @Autowired
    private JjgFbgcSdgcCqtqdMapper jjgFbgcSdgcCqtqdMapper;

    @Autowired
    private JjgFbgcLjgcHdgqdService jjgFbgcLjgcHdgqdService;

    @Value(value = "${jjgys.path.filepath}")
    private String filepath;

    @Autowired
    private SysUserService sysUserService;


    @Override
    public boolean generateJdb(CommonInfoVo commonInfoVo) throws IOException, ParseException {
        XSSFWorkbook wb = null;
        String proname = commonInfoVo.getProname();
        String htd = commonInfoVo.getHtd();
        String fbgc = commonInfoVo.getFbgc();
        //获取数据
        QueryWrapper<JjgFbgcSdgcCqtqd> wrapper=new QueryWrapper<>();
        wrapper.like("proname",proname);
        wrapper.like("htd",htd);
        wrapper.like("fbgc",fbgc);
        String userid = commonInfoVo.getUserid();

        //查询用户类型
        QueryWrapper<SysUser> wrapperuser = new QueryWrapper<>();
        wrapperuser.eq("id",userid);
        SysUser one = sysUserService.getOne(wrapperuser);
        String type = one.getType();
        if ("2".equals(type) || "4".equals(type)){
            //公司管理员
            Long deptId = one.getDeptId();
            QueryWrapper<SysUser> wrapperid = new QueryWrapper<>();
            wrapperid.eq("dept_id",deptId);
            List<SysUser> list = sysUserService.list(wrapperid);
            //拿到部门下所有用户的id
            List<Long> idlist = new ArrayList<>();

            if (list!=null){
                for (SysUser user : list) {
                    Long id = user.getId();
                    idlist.add(id);
                }
            }
            wrapper.in("userid",idlist);
        }else if ("3".equals(type)){
            //普通用户
            //String username = project.getUsername();
            wrapper.like("userid",userid);
        }
        wrapper.orderByAsc("sdmc","bw1");
        List<JjgFbgcSdgcCqtqd> data = jjgFbgcSdgcCqtqdMapper.selectList(wrapper);
        //鉴定表要存放的路径
        File f = new File(filepath+File.separator+proname+File.separator+htd+File.separator+"38隧道衬砌砼强度.xlsx");
        //健壮性判断如果没有数据返回"请导入数据"
        if (data == null || data.size()==0){
            return false;
        }else {
            //存放鉴定表的目录
            File fdir = new File(filepath+File.separator+proname+File.separator+htd);
            if(!fdir.exists()){
                //创建文件根目录
                fdir.mkdirs();
            }
            try {
               /* File directory = new File("service-system/src/main/resources/static");
                String reportPath = directory.getCanonicalPath();
                String name = "涵洞砼强度.xlsx";
                String path =reportPath + File.separator+name;
                Files.copy(Paths.get(path), new FileOutputStream(f));*/
                InputStream inputStream = getClass().getClassLoader().getResourceAsStream("static/涵洞砼强度.xlsx");
                Files.copy(inputStream, f.toPath(), StandardCopyOption.REPLACE_EXISTING);
                FileInputStream out = new FileInputStream(f);
                wb = new XSSFWorkbook(out);
                createTable(gettableNum(data),wb);
                if(DBtoExcel(data,wb)){
                    //设置公式,计算合格点数
                    jjgFbgcLjgcHdgqdService.calculateSheet(wb.getSheet("原始数据"));
                    System.out.println(wb.getNumberOfSheets());
                    for (int j = 0; j < wb.getNumberOfSheets(); j++) {
                        JjgFbgcCommonUtils.updateFormula(wb, wb.getSheetAt(j));
                    }
                    FileOutputStream fileOut = new FileOutputStream(f);
                    wb.write(fileOut);
                    fileOut.flush();
                    fileOut.close();
                }
                inputStream.close();
                out.close();
                wb.close();
                return true;
            }catch (Exception e) {
                if(f.exists()){
                    f.delete();
                }
                throw new JjgysException(20001, "生成鉴定表错误，请检查数据的正确性");
            }

        }

    }

    /**
     *
     * @param data
     * @param wb
     * @return
     * @throws ParseException
     */
    private boolean DBtoExcel(List<JjgFbgcSdgcCqtqd> data,XSSFWorkbook wb) throws ParseException {
        String proname = data.get(0).getProname();
        String htd = data.get(0).getHtd();
        String fbgc = data.get(0).getFbgc();
        XSSFCellStyle cellstyle = JjgFbgcCommonUtils.dBtoExcelUtils(wb);
        //表格数据填写
        XSSFSheet sheet = wb.getSheet("原始数据");
        Date jcsj = data.get(0).getJcsj();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy.MM.dd");
        String testtime = simpleDateFormat.format(jcsj);
        int index = 0;
        int tableNum = 0;
        String sdmc = data.get(0).getSdmc();
        //填写表头
        fillTitleCellData(sheet,tableNum,proname,htd,fbgc);
        //给每个table填写表头
        for(JjgFbgcSdgcCqtqd row:data){
            if (sdmc.equals(row.getSdmc())){
                //比较检测时间，拿到最新的检测时间
                testtime = JjgFbgcCommonUtils.getLastDate(testtime, simpleDateFormat.format(row.getJcsj()));
                if(index/10 == 2){
                    sheet.getRow(tableNum*25+2).getCell(29).setCellValue(testtime);
                    testtime = simpleDateFormat.format(row.getJcsj());
                    tableNum ++;
                    fillTitleCellData(sheet,tableNum,proname,htd,fbgc);
                    index = 0;
                }
                //填写中间下方的普通单元格
                fillCommonCellData(sheet, tableNum, index+5, row,cellstyle);
                index++;
            }else {
                sdmc = row.getSdmc();
                tableNum ++;
                index = 0;
                fillTitleCellData(sheet,tableNum,proname,htd,fbgc);
                testtime = JjgFbgcCommonUtils.getLastDate(testtime, simpleDateFormat.format(row.getJcsj()));
                if(index/10 == 2){
                    sheet.getRow(tableNum*25+2).getCell(29).setCellValue(testtime);
                    testtime = simpleDateFormat.format(row.getJcsj());
                    tableNum ++;
                    fillTitleCellData(sheet,tableNum,proname,htd,fbgc);
                    index = 0;
                }
                //填写中间下方的普通单元格
                fillCommonCellData(sheet, tableNum, index+5, row,cellstyle);
                index++;
            }
        }
        sheet.getRow(tableNum*25+2).getCell(29).setCellValue(testtime);
        return true;
    }

    //填写表头
    private void fillTitleCellData(XSSFSheet sheet, int tableNum,String proname,String htd,String fbgc) {
        if(sheet.getRow(tableNum*25+1) == null || sheet.getRow(tableNum*25+1).getCell(2) == null){
            return;
        }
        sheet.getRow(tableNum*25+1).getCell(2).setCellValue(proname);
        sheet.getRow(tableNum*25+1).getCell(29).setCellValue(htd);
        sheet.getRow(tableNum*25+2).getCell(2).setCellValue(fbgc);
    }


    //填写中间下方的普通单元格
    private void fillCommonCellData(XSSFSheet sheet, int tableNum, int index,JjgFbgcSdgcCqtqd row, XSSFCellStyle cellstyle) {
        sheet.getRow(tableNum*25+index).getCell(0).setCellValue(row.getSdmc());
        sheet.getRow(tableNum*25+index).getCell(1).setCellValue(row.getBw1());
        sheet.getRow(tableNum*25+index).getCell(2).setCellValue(row.getBw2());

        if(!"".equals(row.getCdz1()))
            sheet.getRow(tableNum*25+index).getCell(3).setCellValue(Double.valueOf(row.getCdz1()).intValue());
        if(!"".equals(row.getZ2()))
            sheet.getRow(tableNum*25+index).getCell(4).setCellValue(Double.valueOf(row.getZ2()).intValue());
        if(!"".equals(row.getZ3()))
            sheet.getRow(tableNum*25+index).getCell(5).setCellValue(Double.valueOf(row.getZ3()).intValue());
        if(!"".equals(row.getZ4()))
            sheet.getRow(tableNum*25+index).getCell(6).setCellValue(Double.valueOf(row.getZ4()).intValue());
        if(!"".equals(row.getZ5()))
            sheet.getRow(tableNum*25+index).getCell(7).setCellValue(Double.valueOf(row.getZ5()).intValue());
        if(!"".equals(row.getZ6()))
            sheet.getRow(tableNum*25+index).getCell(8).setCellValue(Double.valueOf(row.getZ6()).intValue());
        if(!"".equals(row.getZ7()))
            sheet.getRow(tableNum*25+index).getCell(9).setCellValue(Double.valueOf(row.getZ7()).intValue());
        if(!"".equals(row.getZ8()))
            sheet.getRow(tableNum*25+index).getCell(10).setCellValue(Double.valueOf(row.getZ8()).intValue());
        if(!"".equals(row.getZ9()))
            sheet.getRow(tableNum*25+index).getCell(11).setCellValue(Double.valueOf(row.getZ9()).intValue());
        if(!"".equals(row.getZ10()))
            sheet.getRow(tableNum*25+index).getCell(12).setCellValue(Double.valueOf(row.getZ10()).intValue());
        if(!"".equals(row.getZ11()))
            sheet.getRow(tableNum*25+index).getCell(13).setCellValue(Double.valueOf(row.getZ11()).intValue());
        if(!"".equals(row.getZ12()))
            sheet.getRow(tableNum*25+index).getCell(14).setCellValue(Double.valueOf(row.getZ12()).intValue());
        if(!"".equals(row.getZ13()))
            sheet.getRow(tableNum*25+index).getCell(15).setCellValue(Double.valueOf(row.getZ13()).intValue());
        if(!"".equals(row.getZ14()))
            sheet.getRow(tableNum*25+index).getCell(16).setCellValue(Double.valueOf(row.getZ14()).intValue());
        if(!"".equals(row.getZ15()))
            sheet.getRow(tableNum*25+index).getCell(17).setCellValue(Double.valueOf(row.getZ15()).intValue());
        if(!"".equals(row.getZ16()))
            sheet.getRow(tableNum*25+index).getCell(18).setCellValue(Double.valueOf(row.getZ16()).intValue());

        if("水平".equals(row.getHtjd())){
            sheet.getRow(tableNum*25+index).getCell(20).setCellValue(row.getHtjd());//U回弹角度
        }
        else{
            sheet.getRow(tableNum*25+index).getCell(20).setCellValue(Double.valueOf(row.getHtjd()).intValue());//U回弹角度
        }
        sheet.getRow(tableNum*25+index).getCell(23).setCellValue(row.getJzm());//X浇筑面
        sheet.getRow(tableNum*25+index).getCell(26).setCellValue(Double.valueOf(row.getThsd()));//AA碳化深度
        sheet.getRow(tableNum*25+index).getCell(28).setCellValue(row.getSfbs());//AC是否泵送
        sheet.getRow(tableNum*25+index).getCell(31).setCellValue(Double.valueOf(row.getSjqd()));//AF设计强度
        sheet.getRow(tableNum*25+index).getCell(31).setCellStyle(cellstyle);

    }

    private void createTable(int tableNum,XSSFWorkbook wb) {
        int record = 0;
        record = tableNum;
        for (int i = 1; i < record; i++) {
            RowCopy.copyRows(wb, "原始数据", "原始数据", 0, 24, i * 25);
        }
        wb.getSheet("原始数据").setColumnHidden(21, true);
        wb.getSheet("原始数据").setColumnHidden(22, true);
        wb.getSheet("原始数据").setColumnHidden(24, true);
        wb.getSheet("原始数据").setColumnHidden(25, true);
        wb.getSheet("原始数据").setColumnHidden(27, true);
        wb.getSheet("原始数据").setColumnHidden(30, true);
        if(record > 1)
            wb.setPrintArea(wb.getSheetIndex("原始数据"), 0, 33, 0, record * 25-1);
    }

    /**
     *
     * @param data
     * @return
     */
    private int gettableNum(List<JjgFbgcSdgcCqtqd> data) {
        if (data!=null){
            Map<String, Integer> resultMap = new HashMap<>();
            for (JjgFbgcSdgcCqtqd map : data) {
                String name = map.getSdmc();
                if (resultMap.containsKey(name)) {
                    resultMap.put(name, resultMap.get(name) + 1);
                } else {
                    resultMap.put(name, 1);
                }
            }
            int num = 0;
            for (Map.Entry<String, Integer> entry : resultMap.entrySet()) {
                int value = entry.getValue();
                if (value%20==0){
                    num += value/20;
                }else {
                    num += value/20+1;
                }
            }
            return num;
        }
        return 0;
        //return size%20 ==0 ? size/20 : size/20+1;
    }

    @Override
    public List<Map<String, Object>> lookJdbjg(CommonInfoVo commonInfoVo) throws IOException {
        String proname = commonInfoVo.getProname();
        String htd = commonInfoVo.getHtd();
        //String fbgc = commonInfoVo.getFbgc();
        String title = "混凝土强度质量鉴定表（回弹法）";
        String sheetname = "原始数据";
        DecimalFormat df = new DecimalFormat("0.00");
        DecimalFormat decf = new DecimalFormat("0.##");
        //获取鉴定表文件
        File f = new File(filepath+File.separator+proname+File.separator+htd+File.separator+"38隧道衬砌砼强度.xlsx");
        if(!f.exists()){
            return null;
        }else {
            XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(f));
            //读取工作表
            XSSFSheet slSheet = xwb.getSheet(sheetname);
            String bt = slSheet.getRow(0).getCell(0).getStringCellValue();//混凝土强度质量鉴定表（回弹法）
            String xmname = slSheet.getRow(1).getCell(2).getStringCellValue();//陕西高速
            String htdname = slSheet.getRow(1).getCell(29).getStringCellValue();//LJ-1

            List<Map<String,Object>> mapList = new ArrayList<>();
            Map<String,Object> jgmap = new HashMap<>();
            if(proname.equals(xmname.toString()) && title.equals(bt.toString()) && htd.equals(htdname.toString())){
                slSheet.getRow(2).getCell(34).setCellType(CellType.STRING);
                slSheet.getRow(2).getCell(35).setCellType(CellType.STRING);
                slSheet.getRow(2).getCell(36).setCellType(CellType.STRING);

                double zds= Double.valueOf(slSheet.getRow(2).getCell(34).getStringCellValue());
                double hgds= Double.valueOf(slSheet.getRow(2).getCell(35).getStringCellValue());
                double hgl= Double.valueOf(slSheet.getRow(2).getCell(36).getStringCellValue());
                String zdsz = decf.format(zds);
                String hgdsz = decf.format(hgds);
                String hglz = df.format(hgl);
                jgmap.put("总点数",zdsz);
                jgmap.put("合格点数",hgdsz);
                jgmap.put("合格率",hglz);
                mapList.add(jgmap);

            }
            return mapList;
        }
    }

    @Override
    public void exportsdcqtqd(HttpServletResponse response) {
        File directory = new File("");// 参数为空
        String courseFile = null;
        try {
            String fileName = "01隧道衬砌砼强度实测数据";
            String sheetName = "原始数据";
            ExcelUtil.writeExcelWithSheets(response, null, fileName, sheetName, new JjgFbgcSdgcCqtqdVo()).finish();
            courseFile = directory.getCanonicalPath();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }finally {
            File t = new File(courseFile+File.separator+"01隧道衬砌砼强度实测数据.xlsx");
            if (t.exists()){
                t.delete();
            }
        }

    }

    @Override
    @Transactional
    public void importsdcqtsd(MultipartFile file, CommonInfoVo commonInfoVo) {
        String htd = commonInfoVo.getHtd();
        try {
            EasyExcel.read(file.getInputStream())
                    .sheet(0)
                    .head(JjgFbgcSdgcCqtqdVo.class)
                    .headRowNumber(1)
                    .registerReadListener(
                            new ExcelHandler<JjgFbgcSdgcCqtqdVo>(JjgFbgcSdgcCqtqdVo.class) {
                                @Override
                                public void handle(List<JjgFbgcSdgcCqtqdVo> dataList) {
                                    int rowNumber=2;
                                    for(JjgFbgcSdgcCqtqdVo sdgcCqtqdVo: dataList)
                                    {
                                        if (StringUtils.isEmpty(sdgcCqtqdVo.getSdmc())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，第"+rowNumber+"行的数据中，隧道名称为空，请修改后重新上传");
                                        }
                                        if (StringUtils.isEmpty(sdgcCqtqdVo.getBw1())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，部位1值为空，请修改后重新上传");
                                        }
                                        if (StringUtils.isEmpty(sdgcCqtqdVo.getBw2())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，部位2值为空，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getCdz1()) || StringUtils.isEmpty(sdgcCqtqdVo.getCdz1())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，测定值1值有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ2()) || StringUtils.isEmpty(sdgcCqtqdVo.getZ2())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值2有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ3()) || StringUtils.isEmpty(sdgcCqtqdVo.getZ3())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值3有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ4()) || StringUtils.isEmpty(sdgcCqtqdVo.getZ4())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值4有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ5()) || StringUtils.isEmpty(sdgcCqtqdVo.getZ5())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值5有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ6())|| StringUtils.isEmpty(sdgcCqtqdVo.getZ6())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值6有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ7())|| StringUtils.isEmpty(sdgcCqtqdVo.getZ7())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值7有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ8())|| StringUtils.isEmpty(sdgcCqtqdVo.getZ8())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值8有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ9())|| StringUtils.isEmpty(sdgcCqtqdVo.getZ9())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值9有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ10()) || StringUtils.isEmpty(sdgcCqtqdVo.getZ10())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值10有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ11()) || StringUtils.isEmpty(sdgcCqtqdVo.getZ11())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值11有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ12()) || StringUtils.isEmpty(sdgcCqtqdVo.getZ12())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值12有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ13())|| StringUtils.isEmpty(sdgcCqtqdVo.getZ13())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值13有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ14()) || StringUtils.isEmpty(sdgcCqtqdVo.getZ14())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值14有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ15())|| StringUtils.isEmpty(sdgcCqtqdVo.getZ15())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值15有误，请修改后重新上传");
                                        }
                                        if (!StringUtils.isNumeric(sdgcCqtqdVo.getZ16())|| StringUtils.isEmpty(sdgcCqtqdVo.getZ16())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，值16有误，请修改后重新上传");
                                        }
                                        if (StringUtils.isEmpty(sdgcCqtqdVo.getHtjd())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，回弹角度值是空的，请修改后重新上传");
                                        }
                                        if (StringUtils.isEmpty(sdgcCqtqdVo.getJzm())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，浇筑面值是空的，请修改后重新上传");
                                        }
                                        if (StringUtils.isEmpty(sdgcCqtqdVo.getThsd())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，碳化深度值是空的，请修改后重新上传");
                                        }
                                        if (StringUtils.isEmpty(sdgcCqtqdVo.getSfbs())) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，是否泵送值是空的，请修改后重新上传");
                                        }
                                        if (StringUtils.isEmpty(sdgcCqtqdVo.getSjqd()) || sdgcCqtqdVo.getSjqd().contains("C")) {
                                            throw new JjgysException(20001, "您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，"+rowNumber+"行的数据中，设计强度值有误（为空或者有C字样），请修改后重新上传");
                                        }
                                        JjgFbgcSdgcCqtqd fbgcSdgcCqtqd = new JjgFbgcSdgcCqtqd();
                                        BeanUtils.copyProperties(sdgcCqtqdVo,fbgcSdgcCqtqd);
                                        fbgcSdgcCqtqd.setCreatetime(new Date());
                                        fbgcSdgcCqtqd.setUserid(commonInfoVo.getUserid());
                                        fbgcSdgcCqtqd.setProname(commonInfoVo.getProname());
                                        fbgcSdgcCqtqd.setHtd(commonInfoVo.getHtd());
                                        fbgcSdgcCqtqd.setFbgc(commonInfoVo.getFbgc());
                                        jjgFbgcSdgcCqtqdMapper.insert(fbgcSdgcCqtqd);
                                        rowNumber++;
                                    }
                                }
                            }
                    ).doRead();
        } catch (IOException e) {
            throw new JjgysException(20001,"您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，解析excel出错，请传入正确格式的excel");
        }catch (NullPointerException e) {
            throw new JjgysException(20001,"您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，请检查数据的正确性或删除文件最后的空数据，然后重新上传");
        }catch (ExcelAnalysisException e) {
            throw new JjgysException(20001,"您上传的"+htd+"合同段中01隧道衬砌砼强度实测数据.xlsx文件，请将检测日期修改为2021/01/01的格式，然后重新上传");
        }

    }

    @Override
    public List<Map<String, Object>> lookjg(CommonInfoVo commonInfoVo) {
        String proname = commonInfoVo.getProname();
        String htd = commonInfoVo.getHtd();
        String sheetname = "原始数据";
        DecimalFormat decf = new DecimalFormat("0.##");
        //获取鉴定表文件
        File f = new File(filepath + File.separator + proname + File.separator + htd + File.separator + "38隧道衬砌砼强度.xlsx");
        if (!f.exists()) {
            return null;
        }else {
            List<Map<String, Object>> data = new ArrayList<>();

            try (FileInputStream fis = new FileInputStream(f)) {
                Workbook workbook = WorkbookFactory.create(fis);
                Sheet sheet = workbook.getSheet(sheetname); // 假设数据在第一个工作表中

                Iterator<Row> rowIterator = sheet.iterator();
                int rowNum = 0;
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    rowNum++;
                    if (rowNum > 4 && row !=null) {
                        Cell cell0 = row.getCell(0);
                        Cell cell34 = row.getCell(34);
                        Cell cell35 = row.getCell(35);
                        Cell cell31 = row.getCell(31);

                        if (cell0 != null && cell34 != null) {
                            String cellValue0 = cell0.getStringCellValue();
                            cell31.setCellType(CellType.STRING);
                            String cellValue31 = cell31.getStringCellValue();
                            String cellValue34 = String.valueOf(decf.format(cell34.getNumericCellValue()));
                            String cellValue35 = String.valueOf(decf.format(cell35.getNumericCellValue()));

                            if (!cellValue0.isEmpty() && !cellValue34.isEmpty()) {
                                Map map = new HashMap();
                                map.put("qlmc",cellValue0);
                                map.put("zds",cellValue34);
                                map.put("hgds",cellValue35);
                                map.put("sjqd",cellValue31);

                                data.add(map);
                            }


                        }
                    }

                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return data;

        }
    }

    @Override
    public List<Map<String, Object>> getsdnum(String proname, String htd) {
        List<Map<String,Object>> list = jjgFbgcSdgcCqtqdMapper.getsdnum(proname,htd);
        return list;
    }

    @Override
    public int selectnum(String proname, String htd) {
        int selectnum = jjgFbgcSdgcCqtqdMapper.selectnum(proname, htd);
        return selectnum;
    }

    @Override
    public int selectnumname(String proname) {
        int selectnum = jjgFbgcSdgcCqtqdMapper.selectnumname(proname);
        return selectnum;
    }

    @Override
    public int createMoreRecords(List<JjgFbgcSdgcCqtqd> data, String userID) {
        return ReceiveUtils.createMore(data, jjgFbgcSdgcCqtqdMapper, userID);
    }
}
